﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Data
{
    public class SqlCrud
    {
        private static SqlCrud instance;

        private SqlCrud() { }

        public static SqlCrud getInstance
        {
            get
            {
                if (instance == null)
                {
                    instance = new SqlCrud();
                }
                return instance;
            }
        }
        /// <summary>
        /// Montar comando insert -> Insert into " + tabela + " (" + concatenarString(campos, false) + ") values (" + concatenarString(values, true) + ")
        /// </summary>
        /// <param name="tabela"></param>
        /// <param name="campos"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public string insert(string tabela, List<string> campos, List<Object> values)
        {
            return "Insert into " + tabela + " (" + concatenarString(campos, false) + ") values (" + concatenarString(values, true) + ")";
        }

        /// <summary>
        /// Montar select * -> Select * From " + tabela
        /// </summary>
        /// <param name="tabela"></param>
        /// <returns></returns>
        public string selectAll(string tabela)
        {
            return "Select * From " + tabela;
        }

        /// <summary>
        /// Montar select * com condição -> Select * From " + tabela + " where " + condicao
        /// </summary>
        /// <param name="tabela"></param>
        /// <param name="condicao"></param>
        /// <returns></returns>
        public string selectAll(string tabela, string condicao)
        {
            return "Select * From " + tabela + " where " + condicao;
        }

        public string selectLike(string tabela, string referencia, string campo, object valor)
        {
            return "Select "+ campo + " From " + tabela + " where "+referencia+ " LIKE '"+ valor +"'" ;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tabela"></param>
        /// <param name="referencia"></param>
        /// <param name="campo"></param>
        /// <param name="valor"></param>
        /// <returns></returns>
        public string selectCampo(string tabela, string referencia, string campo, object valor)
        {
            if (valor is string)
            {
                return "Select " + campo + " From " + tabela + " where " + referencia + " = '" + valor + "'"; 
            }
            else
            {
                return "Select " + campo + " From " + tabela + " where " + referencia + " = " + valor;
            }
        }

        public string select(string tabela,string condicao)
        {
            return "Select * From " + tabela+ " Where " + condicao;
        }

        public string select(List<string> tabelas, List<string> campos, string condicao)
        {
            return "Select " + concatenarString(campos, false) + " From " + concatenarString(tabelas, false) + " Where " + condicao;
        }
        
        public string select(List<string> tabelas, List<string> campos)
        {
            return "Select " + concatenarString(campos, false) + " From " + concatenarString(tabelas, false);
        }

        public string select(List<string> tabelas,string condicao)
        {
            return "Select * From " + concatenarString(tabelas, false) + " Where " + condicao; 
        }

        public string select(string tabela, List<string> campos)
        {
            return "Select " + concatenarString(campos, false) + " From " + tabela;
        }

        public string select(string tabela, List<string> campos, string condicao)
        {
            return "Select " + concatenarString(campos, false) + " From " + tabela + " Where " + condicao;
        }

        public string selectLike(List<string> tabelas, string PK, List<string> campos, string valor)
        {
            return "Select " + concatenarString(campos, false) + " From " + concatenarString(tabelas, false) + " where " + PK + " LIKE '" + valor + "'";
        }

        public string selectMax(string tabela)
        {
            return "select ident_current ( '"+ tabela + "')";
        }

        public string updateAll(string tabela, List<string> alteracoes)
        {
            return "Update " + tabela + " set " + concatenarString(alteracoes, false);
        }

        public string updateById(string tabela, List<string> alteracoes, string PK, Object id)
        {
            return "Update " + tabela + " set " + concatenarString(alteracoes, false) + " Where " + PK + " = '" + id + "'";
        }

        public string updateByIdComposto(string tabela, List<string> alteracoes, string PK, Object id, string PK2, Object id2)
        {
            return "Update " + tabela + " set " + concatenarString(alteracoes, false) + " Where " + PK + " = " + id +
                " and " + PK2 + " = " + id2;
        }

        public string deleteAll(string tabela)
        {
            return "Delete From " + tabela;
        }

        public string deleteById(string tabela, string PK, Object id)
        {
            if (id.GetType() == typeof(string))
            {
                return "Delete From " + tabela + " Where " + PK + " = '" + id + "'";
            }
            else
            {
                return "Delete From " + tabela + " Where " + PK + " = " + id;
            }
        }

        public string deleteByIdComposto(string tabela, string PK1, Object id1, string PK2, object id2)
        {
            string sql = "Delete From " + tabela + " Where ";
            if (id1.GetType() == typeof(string))
            {
                sql += PK1 + " = '" + id1 + "'";
            }
            else
            {
                sql += PK1 + " = " + id1;
            }

            sql += " and ";

            if (id2.GetType() == typeof(string))
            {
                sql += PK2 + " = '" + id2 + "'";
            }
            else
            {
                sql += PK2 + " = " + id2;
            }
            return sql;
        }

        private string concatenarString<T>(List<T> lista, bool aspas)
        {
            string str = "";
            int contador = 0;
            foreach (var item in lista)
            {
                if (item != null && item.GetType() == typeof(string) && aspas) // completar com mas tipos que precisam de ''
                {
                    str += "'" + item + "'";
                }
                else
                {
                    str += item;
                }

                contador++;
                str += (contador != lista.Count) ? ", " : "";
               
            }
            return str;
        }


    }
}
